using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Write.Api.Application.Command.ERP.PurchasingManagement.RawMaterialSuppliers;
using System.Data;

namespace RBAC.Write.Api.Application.Handler.ERP.PurchasingManagement.RawMaterialSuppliers
{
    /// <summary>
    /// 修改供应商信息处理
    /// </summary>
    public class UpdateRawMaterialSupplierCommandHandlers : IRequestHandler<UpdateRawMaterialSupplierCommand, APIResult<int>>
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<UpdateRawMaterialSupplierCommandHandlers> _logger;

        public UpdateRawMaterialSupplierCommandHandlers(IDbConnectionFactory connectionFactory, ILogger<UpdateRawMaterialSupplierCommandHandlers> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<APIResult<int>> Handle(UpdateRawMaterialSupplierCommand request, CancellationToken cancellationToken)
        {
            var res = new APIResult<int>();

            if (request.Id <= 0)
            {
                res.Code = APIEnums.Fail;
                res.Msg = "供应商ID无效";
                return res;
            }

            try
            {
                using var conn = _connectionFactory.CreateConnectionWithRetry();
                using var tran = conn.BeginTransaction();

                try
                {
                    // 1) 更新 Supplier 主表（用 COALESCE 仅覆盖提供的字段）
                    var updateSupplierSql = @"UPDATE Supplier SET
                        Supplier_Name   = COALESCE(@Supplier_Name, Supplier_Name),
                        Supplier_Alias  = COALESCE(@Supplier_Alias, Supplier_Alias),
                        Short_Name      = COALESCE(@Short_Name, Short_Name),
                        Province_City   = COALESCE(@Province_City, Province_City),
                        Supply_Category = COALESCE(@Supply_Category, Supply_Category),
                        Supplier_Level  = COALESCE(@Supplier_Level, Supplier_Level),
                        Supply_Products = COALESCE(@Supply_Products, Supply_Products),
                        Company_Address = COALESCE(@Company_Address, Company_Address),
                        Postal_Code     = COALESCE(@Postal_Code, Postal_Code),
                        Phone           = COALESCE(@Phone, Phone),
                        Remarks         = COALESCE(@Remarks, Remarks),
                        Status          = COALESCE(@Status, Status)
                    WHERE Id = @Id AND IsDeleted = 0;";

                    var affected = await conn.ExecuteAsync(new CommandDefinition(updateSupplierSql, new
                    {
                        request.Supplier_Name,
                        request.Supplier_Alias,
                        request.Short_Name,
                        request.Province_City,
                        request.Supply_Category,
                        request.Supplier_Level,
                        request.Supply_Products,
                        request.Company_Address,
                        request.Postal_Code,
                        request.Phone,
                        request.Remarks,
                        request.Status,
                        request.Id
                    }, tran, cancellationToken: cancellationToken));

                    // 2) 业务联系人：存在则更新，不存在且提供数据则插入一条
                    if (!string.IsNullOrWhiteSpace(request.BusinessContact_Name) || !string.IsNullOrWhiteSpace(request.BusinessMobile_Phone))
                    {
                        var queryBusinessIdSql = "SELECT Id FROM SupplierBusiness WHERE Supplier_Id=@Supplier_Id AND IsDel=0 LIMIT 1";
                        var businessId = await conn.QueryFirstOrDefaultAsync<long?>(new CommandDefinition(queryBusinessIdSql, new { Supplier_Id = request.Id }, tran, cancellationToken: cancellationToken));

                        if (businessId.HasValue && businessId.Value > 0)
                        {
                            var updateBusinessSql = @"UPDATE SupplierBusiness SET
                                    Contact_Name = COALESCE(@Contact_Name, Contact_Name),
                                    Mobile_Phone = COALESCE(@Mobile_Phone, Mobile_Phone)
                                WHERE Id=@Id";
                            await conn.ExecuteAsync(new CommandDefinition(updateBusinessSql, new
                            {
                                Id = businessId.Value,
                                Contact_Name = request.BusinessContact_Name,
                                Mobile_Phone = request.BusinessMobile_Phone
                            }, tran, cancellationToken: cancellationToken));
                        }
                        else
                        {
                            var insertBusinessSql = @"INSERT INTO SupplierBusiness (Supplier_Id, Contact_Name, Mobile_Phone, IsDel)
                                                     VALUES (@Supplier_Id, @Contact_Name, @Mobile_Phone, 0);";
                            await conn.ExecuteAsync(new CommandDefinition(insertBusinessSql, new
                            {
                                Supplier_Id = request.Id,
                                Contact_Name = request.BusinessContact_Name,
                                Mobile_Phone = request.BusinessMobile_Phone
                            }, tran, cancellationToken: cancellationToken));
                        }
                    }

                    // 3) 财务联系人：存在则更新，不存在且提供数据则插入一条
                    if (!string.IsNullOrWhiteSpace(request.FinanceContact_Name) || !string.IsNullOrWhiteSpace(request.FinanceMobile_Phone))
                    {
                        var queryFinanceIdSql = "SELECT Id FROM SupplierFinance WHERE Supplier_Id=@Supplier_Id AND IsDel=0 LIMIT 1";
                        var financeId = await conn.QueryFirstOrDefaultAsync<long?>(new CommandDefinition(queryFinanceIdSql, new { Supplier_Id = request.Id }, tran, cancellationToken: cancellationToken));

                        if (financeId.HasValue && financeId.Value > 0)
                        {
                            var updateFinanceSql = @"UPDATE SupplierFinance SET
                                    Contact_Name = COALESCE(@Contact_Name, Contact_Name),
                                    Mobile_Phone = COALESCE(@Mobile_Phone, Mobile_Phone)
                                WHERE Id=@Id";
                            await conn.ExecuteAsync(new CommandDefinition(updateFinanceSql, new
                            {
                                Id = financeId.Value,
                                Contact_Name = request.FinanceContact_Name,
                                Mobile_Phone = request.FinanceMobile_Phone
                            }, tran, cancellationToken: cancellationToken));
                        }
                        else
                        {
                            var insertFinanceSql = @"INSERT INTO SupplierFinance (Supplier_Id, Contact_Name, Mobile_Phone, IsDel)
                                                     VALUES (@Supplier_Id, @Contact_Name, @Mobile_Phone, 0);";
                            await conn.ExecuteAsync(new CommandDefinition(insertFinanceSql, new
                            {
                                Supplier_Id = request.Id,
                                Contact_Name = request.FinanceContact_Name,
                                Mobile_Phone = request.FinanceMobile_Phone
                            }, tran, cancellationToken: cancellationToken));
                        }
                    }

                    // 4) 开票信息：存在则更新，不存在且提供数据则插入一条
                    if (!string.IsNullOrWhiteSpace(request.Bank_Name) ||
                        !string.IsNullOrWhiteSpace(request.Bank_Address) ||
                        !string.IsNullOrWhiteSpace(request.Account_Number) ||
                        !string.IsNullOrWhiteSpace(request.Tax_Number))
                    {
                        var queryInvoiceIdSql = "SELECT Id FROM SupplierInvoice WHERE Supplier_Id=@Supplier_Id AND IsDel=0 LIMIT 1";
                        var invoiceId = await conn.QueryFirstOrDefaultAsync<long?>(new CommandDefinition(queryInvoiceIdSql, new { Supplier_Id = request.Id }, tran, cancellationToken: cancellationToken));

                        if (invoiceId.HasValue && invoiceId.Value > 0)
                        {
                            var updateInvoiceSql = @"UPDATE SupplierInvoice SET
                                    Bank_Name     = COALESCE(@Bank_Name, Bank_Name),
                                    Bank_Address  = COALESCE(@Bank_Address, Bank_Address),
                                    Account_Number= COALESCE(@Account_Number, Account_Number),
                                    Tax_Number    = COALESCE(@Tax_Number, Tax_Number)
                                WHERE Id=@Id";
                            await conn.ExecuteAsync(new CommandDefinition(updateInvoiceSql, new
                            {
                                Id = invoiceId.Value,
                                request.Bank_Name,
                                request.Bank_Address,
                                request.Account_Number,
                                request.Tax_Number
                            }, tran, cancellationToken: cancellationToken));
                        }
                        else
                        {
                            var insertInvoiceSql = @"INSERT INTO SupplierInvoice (Supplier_Id, Bank_Name, Bank_Address, Account_Number, Tax_Number, IsDel)
                                                     VALUES (@Supplier_Id, @Bank_Name, @Bank_Address, @Account_Number, @Tax_Number, 0);";
                            await conn.ExecuteAsync(new CommandDefinition(insertInvoiceSql, new
                            {
                                Supplier_Id = request.Id,
                                request.Bank_Name,
                                request.Bank_Address,
                                request.Account_Number,
                                request.Tax_Number
                            }, tran, cancellationToken: cancellationToken));
                        }
                    }

                    tran.Commit();

                    res.Code = APIEnums.Success;
                    res.Msg = "修改供应商成功";
                    res.Data = affected; // 返回主表影响行数
                    return res;
                }
                catch (Exception ex)
                {
                    try { tran.Rollback(); } catch { }
                    _logger.LogError(ex, "修改供应商失败");
                    res.Code = APIEnums.Error;
                    res.Msg = $"修改供应商失败: {ex.Message}";
                    return res;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "数据库连接或事务失败");
                res.Code = APIEnums.Error;
                res.Msg = $"数据库异常: {ex.Message}";
                return res;
            }
        }
    }
}
